/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.CES.DAOImplementation;

import com.bionic.CES.DAO.ICurrencyDAO;
import com.bionic.CES.beans.Currency;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLCurrencyDAO extends MYSQLDAO implements ICurrencyDAO {

    @Override
    public void add(Currency currency) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO currency "
                    + "(`ShortName`, `FullName`, `Code`)  "
                    + "VALUES(?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setString(1, currency.getShortName());
            ptmt.setString(2, currency.getFullName());
            ptmt.setInt(3, currency.getCode());
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Added Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Currency currency) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE currency SET `ShortName`=?, `FullName`=?, `Code`=?"
                    + " WHERE `ShortName`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setString(1, currency.getShortName());
            ptmt.setString(2, currency.getFullName());
            ptmt.setInt(3, currency.getCode());
            ptmt.setString(4, currency.getShortName());
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Updated Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteByShortName(String ShortName) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM currency WHERE `ShortName`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setString(1, ShortName);
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data deleted Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Currency> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Currency> result = new ArrayList();
            String queryString = "SELECT * FROM currency";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Currency currency = new Currency();
                currency.setShortName(resultSet.getString("ShortName"));
                currency.setFullName(resultSet.getString("FullName"));
                currency.setCode(resultSet.getInt("Code"));
                result.add(currency);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Currency> findByDynamicSelect(Currency currency) throws SQLException {
        connection = getConnection();
        try {
            List<Currency> result = new ArrayList();
            String queryString = "SELECT * FROM currency WHERE ";
            int i = 0;

            if (currency.getShortName() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`ShortName`=\"" + currency.getShortName() + "\"";
                i++;
            }
            if (currency.getFullName() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`FullName`=\"" + currency.getFullName() + "\"";
                i++;
            }
            if (currency.getCode() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Code`=" + currency.getCode();
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                currency = new Currency();
                currency.setShortName(resultSet.getString("ShortName"));
                currency.setFullName(resultSet.getString("FullName"));
                currency.setCode(resultSet.getInt("Code"));
                result.add(currency);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }
}
